What is the difference between a left outer join and a right outer join?

 

It is best to illustrate the differences between left outer joins and right outer joins by use of an example. Here we have 2 tables that we will use for our example:

Employee Location
EmpID EmpName
13 Jason
8 Alex
3 Ram
17 Babu
25 Johnson
EmpID EmpLoc
13 San Jose
8 Los Angeles
3 Pune, India
17 Chennai, India
39 Bangalore, India

For the purpose of our example, it is important to note that the very last employee in the Employee table (Johnson, who has an ID of 25) is not in the Location table. Also, no one from the Employee table is from Bangalore (the employee with ID 39 is not in the Employee table). These facts will be significant in the discussion that follows.

A left outer join

Using the tables above, here is what the SQL for a left outer join would look like:

select * from employee left outer join location 
on employee.empID = location.empID;

In the SQL above, we are joining on the condition that the employee ID’s match in the tables Employee and Location. So, we will be essentially combining 2 tables into 1, based on the condition that the employee ID’s match. Note that we can get rid of the "outer" in left outer join, which will give us the SQL below. This is equivalent to what we have above.

select * from employee left join location 
on employee.empID = location.empID;

What do left and right mean?

A left outer join retains all of the rows of the “left” table, regardless of whether there is a row that matches on the “right” table. What are the “left” and “right” tables? That’s easy – the “left” table is simply the table that comes first in the join statement – in this case it is the Employee table, it’s called the “left” table because it appears to the left of the keyword “join”. So, the “right” table in this case would be Location. The SQL above will give us the result set shown below.

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
25 Johnson NULL NULL

As you can see from the result set, all of the rows from the “left” table (Employee) are returned when we do a left outer join. The last row of the Employee table (which contains the "Johson" entry) is displayed in the results even though there is no matching row in the Location table. As you can see, the non-matching columns in the last row are filled with a "NULL". So, we have "NULL" as the entry wherever there is no match.

Subscribe to our newsletter on the left to receive more free interview questions!

What is a right outer join?

A right outer join is pretty much the same thing as a left outer join, except that all the rows from the right table are displayed in the result set, regardless of whether or not they have matching values in the left table. This is what the SQL looks like for a right outer join:

select * from employee right outer join location 
on employee.empID = location.empID;

// taking out the "outer", this also works:

select * from employee right join location 
on employee.empID = location.empID;

Using the tables presented above, we can show what the result set of a right outer join would look like:

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
NULL NULL 39 Bangalore, India

We can see that the last row returned in the result set contains the row that was in the Location table, but which had no matching “empID” in the Employee table (the "Bangalore, India" entry). Because there is no row in the Employee table that has an employee ID of "39", we have NULL’s in that row for the Employee columns.

So, what is the difference between the right and left outer joins?

The difference is simple – in a left outer join, all of the rows from the “left” table will be displayed, regardless of whether there are any matching columns in the “right” table. In a right outer join, all of the rows from the “right” table will be displayed, regardless of whether there are any matching columns in the “left” table. Hopefully the example that we gave above help clarified this as well.

Should I use a right outer join or a left outer join?

Actually, it doesn’t matter. The right outer join does not add any functionality that the left outer join didn’t already have, and vice versa. All you would have to do to get the same results from a right outer join and a left outer join is switch the order in which the tables appear in the SQL statement. If that’s confusing, just take a closer look at the examples given above.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.